package com.eascs.web.o2o.report.service;

import com.alibaba.dubbo.config.annotation.Reference;
import com.eascs.app.log.Logger;
import com.eascs.app.log.LoggerFactory;
import com.eascs.workflow.common.entity.sys.ActOrgEntity;
import com.eascs.workflow.common.service.sys.SysOrgService;
import org.springframework.stereotype.Service;

import javax.inject.Inject;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

@Service
public class BuildDataService {
	
	protected final Logger logger =LoggerFactory.getLogger(getClass());
	@Inject
	BaseReportService baseReportService;
	@Reference
	SysOrgService sysOrgService;
	
	
	
	
	
	
	
	
	

	public List<Object[]> getBankData(Date month) {
		logger.info("开始同步银行明细报表");
		//删除之前的数据
		baseReportService.isnertNewBusiness("delete from bank_detail");
		//银行,审批额度
		String sql1="sql.BuildExcelService.sql1";
		int count=0;
		try {
			List<Object[]> data1 = baseReportService.findObjectsList(sql1);
			for (int i = 0; i < data1.size(); i++) {//更新报表每一列的数据
				count++;
				Object[] b=data1.get(i);
				double approvalAmount =0;
				if(b!=null){
					String bank=(String) b[0];
					if(bank!=null&&bank.equals("中国银行")){
						approvalAmount=1000000000;
					}else if(bank!=null&&bank.equals("国安村镇银行")){
						approvalAmount=200000000;
					}else if(bank!=null&&bank.equals("中国建设银行")){
						approvalAmount=500000000;
					}else if(bank!=null&&bank.equals("江西银行")){
						approvalAmount=1000000000;
					}else if(bank!=null&&bank.equals("郑州银行")){
						approvalAmount=1000000000;
					}
					baseReportService.isnertNewBusiness("insert into bank_detail(id,refund_bank,approval_amount) values(?,?,?)",count, bank,approvalAmount);
				}
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：放款银行,审批额度=========================================");
			throw e;
		}
		
		//累计投放总量（累计笔数，金额）
		String sql2="sql.BuildExcelService.sql2";
		try {
			List<Object[]> data2 = baseReportService.findObjectsList(sql2);
			for (int i = 0; i < data2.size(); i++) {//更新报表每一列的数据
				Object[] b=data2.get(i);
				baseReportService.isnertNewBusiness("update bank_detail set loan_num = ?,loan_amount=? where refund_bank = ? ", b[1],b[2],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：累计投放总量（累计笔数，金额）统计异常=========================================");
			throw e;
		}
		//累计投放总量(余额)
		String sql3="sql.BuildExcelService.sql3";
		try {
			List<Object[]> data3 = baseReportService.findObjectsList(sql3);
			for (int i = 0; i < data3.size(); i++) {//更新报表每一列的数据
				Object[] b=data3.get(i);
				baseReportService.isnertNewBusiness("update bank_detail set loan_balance = ? where refund_bank = ? ", b[1],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：累计投放总量(余额)统计异常=========================================");
			throw e;
		}
		
		//当月投放(累计笔数，金额)
		String sql4="sql.BuildExcelService.sql4";
		try {
			List<Object[]> data4 = baseReportService.findObjectsList(sql4,month,month);
			for (int i = 0; i < data4.size(); i++) {//更新报表每一列的数据
				Object[] b=data4.get(i);
				baseReportService.isnertNewBusiness("update bank_detail set month_loan_num = ? ,month_loan_amount=?  where refund_bank = ? ", b[1],b[2],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：当月投放(累计笔数，金额)统计异常=========================================");
			throw e;
		}
		
		//剩余额度
		String sql5="sql.BuildExcelService.sql5";
		try {
			List<Object[]> data5 = baseReportService.findObjectsList(sql5);
			for (int i = 0; i < data5.size(); i++) {//更新报表每一列的数据
				Object[] b=data5.get(i);
				if(b!=null){
					BigDecimal residualAmount=(BigDecimal) b[1];
					double sum=0;
					if(residualAmount==null||residualAmount.doubleValue()<0){
						sum=0.0;
					}else{
						sum=residualAmount.doubleValue();
					}
					baseReportService.isnertNewBusiness("update bank_detail set residual_amount = ? where refund_bank = ? ",sum,  b[0]);
				}
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：剩余额度 统计异常=========================================");
			throw e;
		}
		
	
		String sql6="sql.BuildExcelService.sql6";
		List<Object[]> datas =baseReportService.findObjectsList(sql6);
		logger.info("结束同步银行明细报表");
		return datas;
	}
	
	
	
	
	
	
	
	public List<Object[]> getPlatData(Date month) {
		logger.info("开始同步月报-平台明细报表");
		//删除之前的数据
		baseReportService.isnertNewBusiness("delete from plat_detail");
		
		//月报-平台明细
		String sql1="sql.BuildExcelService.sql7";
		logger.info("插入月报-平台明细的平台以及省区等字段开始......");
		try {
			baseReportService.isnertNewBusiness(sql1,month);
		} catch (Exception e) {
			logger.error("插入月报-平台明细的平台以及省区等字段出现异常......");
			throw e;
		}
		logger.info("插入月报-平台明细的平台以及省区等字段结束......");
		
		//本年新客户投放(笔数,金额)
		String sql2="sql.BuildExcelService.sql8";
		try {
			List<Object[]> data2 = baseReportService.findObjectsList(sql2,month);
			for (int i = 0; i < data2.size(); i++) {//更新报表每一列的数据
				Object[] b=data2.get(i);
				baseReportService.isnertNewBusiness("update plat_detail set loan_num_new_cus_year = ?,loan_amount_new_cus_year=? where ltd_name = ? ", b[1],b[2],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：本年新客户投放(笔数,金额)统计异常=========================================");
			throw e;
		}
		
		//新客户投放量占比%
		String sql3="sql.BuildExcelService.sql9";
		try {
				baseReportService.isnertNewBusiness(sql3);
		} catch (Exception e) {
			logger.info("BuildExcelService：新客户投放量占比%统计异常=========================================");
			throw e;
		}
		
		//月度累计投放量(累计笔数，金额)
		String sql4="sql.BuildExcelService.sql10";
		try {
			List<Object[]> data4 = baseReportService.findObjectsList(sql4,month,month);
			for (int i = 0; i < data4.size(); i++) {//更新报表每一列的数据
				Object[] b=data4.get(i);
				baseReportService.isnertNewBusiness("update plat_detail set loan_num_month = ? ,loan_amount_month=?  where ltd_name = ? ", b[1],b[2],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：月度累计投放量(累计笔数，金额)统计异常=========================================");
			throw e;
		}
		
		//月度累计投放量(本月新客户投放)
		String sql5="sql.BuildExcelService.sql11";
		try {
			List<Object[]> data5 = baseReportService.findObjectsList(sql5,month,month);
			for (int i = 0; i < data5.size(); i++) {//更新报表每一列的数据
				Object[] b=data5.get(i);
					baseReportService.isnertNewBusiness("update plat_detail set loan_num_new_cus_month = ? ,loan_amount_new_cus_month=?  where ltd_name = ? ", b[1],b[2],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：月度累计投放量(本月新客户投放) 统计异常=========================================");
			throw e;
		}
		
		
		//新客户投放量占比%
		String sql6="sql.BuildExcelService.sql12";
		try {
				baseReportService.isnertNewBusiness(sql6);
		} catch (Exception e) {
			logger.info("BuildExcelService：新客户投放量占比%统计异常=========================================");
			throw e;
		}
	
		String sql7="sql.BuildExcelService.sql13";
		List<Object[]> datas =baseReportService.findObjectsList(sql7);
		logger.info("结束同步月报-平台明细报表");
		return datas;
	}
	
	
	
	public List<Object[]> getProvinceData(Date month) {
		logger.info("开始同步月报-省区明细报表");
		//删除之前的数据
		baseReportService.isnertNewBusiness("delete from province_detail");
		
		//月报-省区明细
		String sql1="sql.BuildExcelService.sql14";
		logger.info("插入月报-省区明细字段开始......");
		try {
			baseReportService.isnertNewBusiness(sql1,month,month);
		} catch (Exception e) {
			logger.error("插入月报-省区明细的省区字段出现异常......");
			throw e;
		}
		logger.info("插入月报-省区明细的省区字段结束......");
		
		//新增笔数(笔数,金额)
		String sql2="sql.BuildExcelService.sql15";
		try {
			List<Object[]> data2 = baseReportService.findObjectsList(sql2,month,month);
			for (int i = 0; i < data2.size(); i++) {//更新报表每一列的数据
				Object[] b=data2.get(i);
				baseReportService.isnertNewBusiness("update province_detail set add_num = ?,add_sum=? where province = ? ", b[1],b[2],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：新增笔数(笔数,金额)统计异常=========================================");
			throw e;
		}
		
		//续贷笔数(笔数,金额)
		String sql3="sql.BuildExcelService.sql16";
		try {
			List<Object[]> data3 = baseReportService.findObjectsList(sql3,month,month);
			for (int i = 0; i < data3.size(); i++) {//更新报表每一列的数据
				Object[] b=data3.get(i);
				baseReportService.isnertNewBusiness("update province_detail set extend_num = ?,extend_sum=? where province = ? ", b[1],b[2],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：续贷笔数(笔数,金额)统计异常=========================================");
			throw e;
		}
			
		//下辖平台总量
		String sql4="sql.BuildExcelService.sql17";
		try {
			List<Object[]> data4 = baseReportService.findObjectsList(sql4,month);
			for (int i = 0; i < data4.size(); i++) {//更新报表每一列的数据
				Object[] b=data4.get(i);
				if(b!=null){
					String province=(String) b[0];
					List<ActOrgEntity> list=sysOrgService.getChildOrgByName(province);
					if(list!=null&&list.size()>0){
						baseReportService.isnertNewBusiness("update province_detail set under_ltd = ? where province = ? ", list.size()-1,province);
					}
				}
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：本年激活平台数量统计异常=========================================");
			throw e;
		}
		
		
		//本年激活平台数量
		String sql5="sql.BuildExcelService.sql18";
		try {
			List<Object[]> data5 = baseReportService.findObjectsList(sql5,month);
			for (int i = 0; i < data5.size(); i++) {//更新报表每一列的数据
				Object[] b=data5.get(i);
				baseReportService.isnertNewBusiness("update province_detail set active_ltd_year = ? where province = ? ", b[1],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：本年激活平台数量统计异常=========================================");
			throw e;
		}
		
		//本月激活平台数量
		String sql6="sql.BuildExcelService.sql19";
		try {
			List<Object[]> data6 = baseReportService.findObjectsList(sql6,month,month);
			for (int i = 0; i < data6.size(); i++) {//更新报表每一列的数据
				Object[] b=data6.get(i);
				baseReportService.isnertNewBusiness("update province_detail set active_ltd_month = ? where province = ? ", b[1],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：本月激活平台数量统计异常=========================================");
			throw e;
		}
		
		
		//笔数合计(笔数,金额)
		String sql7="sql.BuildExcelService.sql20";
		try {
			List<Object[]> data7 = baseReportService.findObjectsList(sql7,month,month);
			for (int i = 0; i < data7.size(); i++) {//更新报表每一列的数据
				Object[] b=data7.get(i);
				baseReportService.isnertNewBusiness("update province_detail set total_num = ?,total_sum=? where province = ? ", b[1],b[2],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：笔数合计(笔数,金额)统计异常=========================================");
			throw e;
		}
		
		
		//广东、海南，合并为广东大区，四川、重庆，合并为川渝大区
		baseReportService.isnertNewBusiness("update province_detail set region= (case when province='广东' then '广东大区' when province='海南' then '广东大区' when province='四川' then '川渝大区' when province='重庆' then '川渝大区' else province  end ) ");
			
		
		//加权投放全国排名
		String sql8="sql.BuildExcelService.sql21";
		try {
			List<Object[]> data8 = baseReportService.findObjectsList(sql8);
			for (int i = 0; i < data8.size(); i++) {//更新报表每一列的数据
				Object[] b=data8.get(i);
				baseReportService.isnertNewBusiness("update province_detail set weighted_rank = ?   where region = ? ", b[1],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：加权投放全国排名统计异常=========================================");
			throw e;
		}
		
		//放款总量排名
		String sql9="sql.BuildExcelService.sql22";
		try {
			List<Object[]> data9 = baseReportService.findObjectsList(sql9);
			for (int i = 0; i < data9.size(); i++) {//更新报表每一列的数据
				Object[] b=data9.get(i);
					baseReportService.isnertNewBusiness("update province_detail set total_loan_rank = ? where region = ? ", b[1],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：放款总量排名统计异常=========================================");
			throw e;
		}
		
		
	
		String sql10="sql.BuildExcelService.sql23";
		List<Object[]> datas =baseReportService.findObjectsList(sql10);
		logger.info("结束同步月报-省区明细报表");
		return datas;
	}
	
	/*
	 * 日报明细
	 */
	
	public List<Object[]> getDayBankData(Date day) {
		logger.info("开始同步日报银行明细报表");
		//删除之前的数据
		baseReportService.isnertNewBusiness("delete from bank_day_detail");
		//银行,审批额度
		String sql1="sql.dayReport.sql11";
		int count=0;
		try {
			List<Object[]> data1 = baseReportService.findObjectsList(sql1);
			for (int i = 0; i < data1.size(); i++) {//更新报表每一列的数据
				count++;
				Object[] b=data1.get(i);
				double approvalAmount =0;
				if(b!=null){
					String bank=(String) b[0];
					if(bank!=null&&bank.equals("中国银行")){
						approvalAmount=1000000000;
					}else if(bank!=null&&bank.equals("国安村镇银行")){
						approvalAmount=200000000;
					}else if(bank!=null&&bank.equals("中国建设银行")){
						approvalAmount=500000000;
					}
					baseReportService.isnertNewBusiness("insert into bank_day_detail(id,refund_bank,approval_amount) values(?,?,?)",count, bank,approvalAmount);
				}
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：放款银行,审批额度=========================================");
			throw e;
		}
		
		//累计投放总量（累计笔数，金额）
		String sql2="sql.dayReport.sql12";
		try {
			List<Object[]> data2 = baseReportService.findObjectsList(sql2);
			for (int i = 0; i < data2.size(); i++) {//更新报表每一列的数据
				Object[] b=data2.get(i);
				baseReportService.isnertNewBusiness("update bank_day_detail set loan_num = ?,loan_amount=? where refund_bank = ? ", b[1],b[2],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：累计投放总量（累计笔数，金额）统计异常=========================================");
			throw e;
		}
		//累计投放总量(余额)
		String sql3="sql.dayReport.sql13";
		try {
			List<Object[]> data3 = baseReportService.findObjectsList(sql3);
			for (int i = 0; i < data3.size(); i++) {//更新报表每一列的数据
				Object[] b=data3.get(i);
				baseReportService.isnertNewBusiness("update bank_day_detail set balance_amount = ? where refund_bank = ? ", b[1],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：累计投放总量(余额)统计异常=========================================");
			throw e;
		}
		
		//当月投放(累计笔数，金额)
		String sql4="sql.dayReport.sql14";
		try {
			List<Object[]> data4 = baseReportService.findObjectsList(sql4,day,day);
			for (int i = 0; i < data4.size(); i++) {//更新报表每一列的数据
				Object[] b=data4.get(i);
				baseReportService.isnertNewBusiness("update bank_day_detail set month_num = ? ,month_amount=?  where refund_bank = ? ", b[1],b[2],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：当月投放(累计笔数，金额)统计异常=========================================");
			throw e;
		}
		
		
		//当日投放(累计笔数，金额)
		String sql5="sql.dayReport.sql15";
		try {
			List<Object[]> data5 = baseReportService.findObjectsList(sql5,day,day,day);
			for (int i = 0; i < data5.size(); i++) {//更新报表每一列的数据
				Object[] b=data5.get(i);
				baseReportService.isnertNewBusiness("update bank_day_detail set day_num = ? ,day_amount=?  where refund_bank = ? ", b[1],b[2],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：当日投放(累计笔数，金额)统计异常=========================================");
			throw e;
		}
		//剩余额度
		String sql6="sql.dayReport.sql16";
		try {
			List<Object[]> data6 = baseReportService.findObjectsList(sql6);
			for (int i = 0; i < data6.size(); i++) {//更新报表每一列的数据
				Object[] b=data6.get(i);
				if(b!=null){
					BigDecimal residualAmount=(BigDecimal) b[1];
					double sum=0;
					if(residualAmount==null||residualAmount.doubleValue()<0){
						sum=0.0;
					}else{
						sum=residualAmount.doubleValue();
					}
					baseReportService.isnertNewBusiness("update bank_day_detail set residual_amount = ? where refund_bank = ? ",sum,  b[0]);
				}
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：剩余额度 统计异常=========================================");
			throw e;
		}
		
	
		String sql7="sql.dayReport.sql17";
		List<Object[]> datas =baseReportService.findObjectsList(sql7);
		logger.info("结束同步银行明细报表");
		return datas;
	}
	
	
	
	public List<Object[]> getDayProvinceData(Date day) {
		logger.info("开始同步日报-省区明细报表");
		//删除之前的数据
		baseReportService.isnertNewBusiness("delete from province_day_detail");
		
		//日报-省区明细
		String sql1="sql.dayReport.sql1";
		logger.info("插入日报-省区明细字段开始......");
		try {
			baseReportService.isnertNewBusiness(sql1,day,day);
		} catch (Exception e) {
			logger.error("插入日报-省区明细的省区字段出现异常......");
			throw e;
		}
		logger.info("插入日报-省区明细的省区字段结束......");
		
		//本月累计放款(笔数,金额)
		String sql2="sql.dayReport.sql2";
		try {
			List<Object[]> data2 = baseReportService.findObjectsList(sql2,day,day);
			for (int i = 0; i < data2.size(); i++) {//更新报表每一列的数据
				Object[] b=data2.get(i);
				baseReportService.isnertNewBusiness("update province_day_detail set loan_sum = ?,loan_amount=? where province = ? ", b[1],b[2],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：本月累计放款(笔数,金额)统计异常=========================================");
			throw e;
		}
		
		//上月累计放款(金额)
		String sql3="sql.dayReport.sql3";
		try {
			List<Object[]> data3 = baseReportService.findObjectsList(sql3,day,day);
			for (int i = 0; i < data3.size(); i++) {//更新报表每一列的数据
				Object[] b=data3.get(i);
				baseReportService.isnertNewBusiness("update province_day_detail set last_loan_amount = ?  where province = ? ", b[1],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：上月累计放款(金额)统计异常=========================================");
			throw e;
		}
			
		//本月累计放款续贷（笔数，金额）
		String sql4="sql.dayReport.sql4";
		try {
			List<Object[]> data4 = baseReportService.findObjectsList(sql4,day,day);
			for (int i = 0; i < data4.size(); i++) {//更新报表每一列的数据
				Object[] b=data4.get(i);
				baseReportService.isnertNewBusiness("update province_day_detail set extend_sum = ?,extend_amount=?  where province = ? ", b[1],b[2],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：本月累计放款续贷（笔数，金额）统计异常=========================================");
			throw e;
		}
		
		
		//本月累计放款新增（笔数，金额）
		String sql5="sql.dayReport.sql5";
		try {
			List<Object[]> data5 = baseReportService.findObjectsList(sql5,day,day);
			for (int i = 0; i < data5.size(); i++) {//更新报表每一列的数据
				Object[] b=data5.get(i);
				baseReportService.isnertNewBusiness("update province_day_detail set add_sum = ?,add_amount=? where province = ? ", b[1],b[2],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：本月累计放款新增（笔数，金额）=========================================");
			throw e;
		}
		
		//当日放款（笔数，金额）
		String sql6="sql.dayReport.sql6";
		try {
			List<Object[]> data6 = baseReportService.findObjectsList(sql6,day,day,day);
			for (int i = 0; i < data6.size(); i++) {//更新报表每一列的数据
				Object[] b=data6.get(i);
				baseReportService.isnertNewBusiness("update province_day_detail set day_loan_sum = ?,day_loan_amount=? where province = ? ", b[1],b[2],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：当日放款（笔数，金额）统计异常=========================================");
			throw e;
		}
		
		baseReportService.isnertNewBusiness("update province_day_detail set add_rage= add_amount/loan_amount");
		
		//广东、海南，合并为广东大区，四川、重庆，合并为川渝大区
		baseReportService.isnertNewBusiness("update province_day_detail set region= (case when province='广东' then '广东大区' when province='海南' then '广东大区' when province='四川' then '川渝大区' when province='重庆' then '川渝大区' else province  end ) ");
			
		
		
		//金额排名
		String sql7="sql.dayReport.sql7";
		try {
			List<Object[]> data7 = baseReportService.findObjectsList(sql7);
			for (int i = 0; i < data7.size(); i++) {//更新报表每一列的数据
				Object[] b=data7.get(i);
				baseReportService.isnertNewBusiness("update province_day_detail set amount_rank = ?   where region = ? ", b[1],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：加权投放全国排名统计异常=========================================");
			throw e;
		}
		
		//加权排名
		String sql8="sql.dayReport.sql8";
		try {
			List<Object[]> data8 = baseReportService.findObjectsList(sql8);
			for (int i = 0; i < data8.size(); i++) {//更新报表每一列的数据
				Object[] b=data8.get(i);
					baseReportService.isnertNewBusiness("update province_day_detail set weighted_rank = ? where region = ? ", b[1],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：放款总量排名统计异常=========================================");
			throw e;
		}
		
		
	
		String sql9="sql.dayReport.sql9";
		List<Object[]> datas =baseReportService.findObjectsList(sql9);
		logger.info("结束同步日报-省区明细报表");
		return datas;
	}
	
	
	
	
	
	
	
	
	public List<Object[]> getCreditManagerRankData() {
		logger.info("开始同步组装平台信贷经理业务排名数据");
		int period =0;//获取当前统计期数
		String sql1="sql.credit.sql1";
		List<Object[]> data1 = baseReportService.findObjectsList(sql1);
		if(data1!=null&&data1.size()>0){
			Object[] o= data1.get(0);
			if(o[0]!=null){
				period = ((int) o[0])+1;
			}else{
				period = 1;
			}
		}else{
			period = 1;
		}
		
		String sql2="sql.credit.sql2";
		logger.info("插入本期新增客户笔数和金额......");
		try {
			List<Object[]> data2 = baseReportService.findObjectsListForService(sql2);
			for (int i = 0; i < data2.size(); i++) {//更新报表每一列的数据
				Object[] b=data2.get(i);
				baseReportService.isnertNewBusiness("insert into credit_manager_rank(id,period,province,credit_manager,period_add_sum,period_add_amount ) values (uuid(),?,?,?,?,?)",period, b[0],b[1],b[2],b[3]);
			}
		} catch (Exception e) {
			logger.error("插入插入本期新增客户笔数和金额出现异常......",e);
		}
		logger.info("插入插入本期新增客户笔数和金额结束......");
		
		
		String sql3="sql.credit.sql3";
		logger.info("插入本期续贷客户笔数和金额......");
		try {
			List<Object[]> data3 = baseReportService.findObjectsListForService(sql3);
			for (int i = 0; i < data3.size(); i++) {//更新报表每一列的数据
				Object[] b=data3.get(i);
				baseReportService.isnertNewBusiness("update  credit_manager_rank set period_extend_sum= ? ,period_extend_amount = ? where period=? and province =? and credit_manager = ? ",b[2],b[3],period,b[0],b[1]);
			}
		} catch (Exception e) {
			logger.error("插入本期续贷客户笔数和金额出现异常......",e);
		}
		logger.info("插入本期续贷客户笔数和金额结束......");
		
		
		String sql4="sql.credit.sql4";
		logger.info("插入本期客户笔数和金额......");
		try {
			List<Object[]> data4 = baseReportService.findObjectsListForService(sql4);
			for (int i = 0; i < data4.size(); i++) {//更新报表每一列的数据
				Object[] b=data4.get(i);
				baseReportService.isnertNewBusiness("update  credit_manager_rank set period_total_sum= ? ,period_total_amount = ? where period=? and province =? and credit_manager = ? ",b[2],b[3],period,b[0],b[1]);
			}
		} catch (Exception e) {
			logger.error("插入本期客户笔数和金额出现异常......",e);
		}
		logger.info("插入本期客户笔数和金额结束......");
		
		String sql5="sql.credit.sql5";
		logger.info("插入本年新增客户笔数和金额......");
		try {
			List<Object[]> data5 = baseReportService.findObjectsListForService(sql5);
			for (int i = 0; i < data5.size(); i++) {//更新报表每一列的数据
				Object[] b=data5.get(i);
				baseReportService.isnertNewBusiness("update  credit_manager_rank set year_add_sum= ? ,year_add_amount = ? where period=? and province =? and credit_manager = ? ",b[2],b[3],period,b[0],b[1]);
			}
		} catch (Exception e) {
			logger.error("插入本年新增客户笔数和金额出现异常......",e);
		}
		logger.info("插入本年新增客户笔数和金额结束......");
		
		
		String sql6="sql.credit.sql6";
		logger.info("插入本年续贷客户笔数和金额......");
		try {
			List<Object[]> data6 = baseReportService.findObjectsListForService(sql6);
			for (int i = 0; i < data6.size(); i++) {//更新报表每一列的数据
				Object[] b=data6.get(i);
				baseReportService.isnertNewBusiness("update  credit_manager_rank set year_extend_sum= ? ,year_extend_amount = ? where period=? and province =? and credit_manager = ? ",b[2],b[3],period,b[0],b[1]);
			}
		} catch (Exception e) {
			logger.error("插入本年续贷客户笔数和金额出现异常......",e);
		}
		logger.info("插入本年续贷客户笔数和金额结束......");
		
		
		String sql7="sql.credit.sql7";
		logger.info("插入本年客户笔数和金额......");
		try {
			List<Object[]> data7 = baseReportService.findObjectsListForService(sql7);
			for (int i = 0; i < data7.size(); i++) {//更新报表每一列的数据
				Object[] b=data7.get(i);
				baseReportService.isnertNewBusiness("update  credit_manager_rank set year_total_sum= ? ,year_total_amount = ? where period=? and province =? and credit_manager = ? ",b[2],b[3],period,b[0],b[1]);
			}
		} catch (Exception e) {
			logger.error("插入本年客户笔数和金额出现异常......",e);
		}
		logger.info("插入本年客户笔数和金额结束......");
		
		
		String sql8="sql.credit.sql8";
		logger.info("插入本期排名......");
		try {
			List<Object[]> data8 = baseReportService.findObjectsList(sql8,period,period);
			for (int i = 0; i < data8.size(); i++) {//更新报表每一列的数据
				Object[] b=data8.get(i);
				baseReportService.isnertNewBusiness("update  credit_manager_rank set period_rank= ?  where period=? and province =? and credit_manager = ? ",b[2],period,b[0],b[1]);
			}
		} catch (Exception e) {
			logger.error("插入本期排名出现异常......",e);
		}
		logger.info("插入本期排名结束......");
		
		String sql9="sql.credit.sql9";
		logger.info("插入本年排名......");
		try {
			List<Object[]> data9 = baseReportService.findObjectsList(sql9,period,period);
			for (int i = 0; i < data9.size(); i++) {//更新报表每一列的数据
				Object[] b=data9.get(i);
				baseReportService.isnertNewBusiness("update  credit_manager_rank set year_rank= ?  where period=? and province =? and credit_manager = ? ",b[2],period,b[0],b[1]);
			}
		} catch (Exception e) {
			logger.error("插入本年排名出现异常......",e);
		}
		logger.info("插入本年排名结束......");
		
		String sql10="sql.credit.sql10";
		if(period>1){
			logger.info("插入排名比较......");
			try {
				List<Object[]> data10 = baseReportService.findObjectsList(sql10,period-1);
				for (int i = 0; i < data10.size(); i++) {//更新报表每一列的数据
					Object[] b=data10.get(i);
					baseReportService.isnertNewBusiness("update  credit_manager_rank set previous_rank= ?  where period=? and province =? and credit_manager = ? ",b[2],period,b[0],b[1]);
				}
			} catch (Exception e) {
				logger.error("插入排名比较出现异常......",e);
			}
			logger.info("插入排名比较结束......");
		}else{
			List<Object[]> data10 = baseReportService.findObjectsList(sql10,period);
			for (int i = 0; i < data10.size(); i++) {//更新报表每一列的数据
				Object[] b=data10.get(i);
				baseReportService.isnertNewBusiness("update  credit_manager_rank set previous_rank= ?  where period=? and province =? and credit_manager = ? ",b[2],period,b[0],b[1]);
			}
		}
		logger.info("插入排名变动开始......");
		try {
			baseReportService.isnertNewBusiness("update  credit_manager_rank set creat_date= now() ,change_rank=period_rank - previous_rank where  period =? ",period);
		} catch (Exception e) {
			logger.error("插入排名变动出现异常......",e);
		}
		logger.info("插入排名变动结束......");
		
		String sql11="sql.credit.sql11";
		List<Object[]> datas =baseReportService.findObjectsList(sql11,period);
		logger.info("结束同步组装平台信贷经理业务排名数据");
		return datas;
	}
	
	
	
	
	public List<Object[]> getProvinceBusinessRankData() {
		logger.info("开始同步全国各省区贷款投放排名");

		String[] arrProvince = {"江苏","河北","江西","云南","北京","山西","福建","河南","安徽","湖北","浙江","川渝","贵州","湖南","山东","广东","广西"};
		double[] arrAmount = {20000,20000,20000,20000,20000,20000,30000,30000,30000,30000,30000,40000,45000,45000,45000,45000,45000};
		
		int period =0;//获取当前统计期数
		String sql1="sql.business.sql1";
		List<Object[]> data1 = baseReportService.findObjectsList(sql1);
		if(data1!=null&&data1.size()>0){
			Object[] o= data1.get(0);
			if(o[0]!=null){
				period = ((int) o[0])+1;
			}else{
				period = 1;
			}
		}else{
			period = 1;
		}
		
		
		String sql2="sql.business.sql2";
		logger.info("插入终端贷新增客户笔数和金额......");
		try {
			List<Object[]> data2 = baseReportService.findObjectsListForService(sql2);
			for (int i = 0; i < data2.size(); i++) {//更新报表每一列的数据
				Object[] b=data2.get(i);
				baseReportService.isnertNewBusiness("insert into province_business_rank(id,period,province,add_zd_sum,add_zd_amount) values (uuid(),?,?,?,?)",period, b[0],b[1],b[2]);
			}
		} catch (Exception e) {
			logger.error("插入终端贷新增客户笔数和金额出现异常......",e);
		}
		logger.info("插入终端贷新增客户笔数和金额结束......");
		
		
		
		logger.info("插入各省区年度任务开始......");
		for (int i = 0; i < arrProvince.length; i++) {
			baseReportService.isnertNewBusiness(" update province_business_rank set year_task =? where period=? and province =? ",arrAmount[i],period,arrProvince[i]);
		}
		logger.info("插入各省区年度任务结束......");
		
		
		
		
		
		String sql3="sql.business.sql3";
		logger.info("插入品牌贷新增客户笔数和金额......");
		try {
			List<Object[]> data3 = baseReportService.findObjectsListForService(sql3);
			for (int i = 0; i < data3.size(); i++) {//更新报表每一列的数据
				Object[] b=data3.get(i);
				baseReportService.isnertNewBusiness("update province_business_rank set add_bp_sum =?,add_bp_amount=?  where period=? and province =?",b[1],b[2],period,b[0]);
			}
		} catch (Exception e) {
			logger.error("插入品牌贷新增客户笔数和金额出现异常......",e);
		}
		logger.info("插入品牌贷新增客户笔数和金额结束......");
		
		
		
		String sql4="sql.business.sql4";
		logger.info("插入新增客户笔数和金额合计......");
		try {
			List<Object[]> data4 = baseReportService.findObjectsListForService(sql4);
			for (int i = 0; i < data4.size(); i++) {//更新报表每一列的数据
				Object[] b=data4.get(i);
				baseReportService.isnertNewBusiness("update province_business_rank set add_total_sum =?,add_total_amount=?  where period=? and province =?",b[1],b[2],period,b[0]);
			}
		} catch (Exception e) {
			logger.error("插入新增客户笔数和金额合计出现异常......",e);
		}
		logger.info("插入新增客户笔数和金额合计结束......");
		
		
		
		
		String sql5="sql.business.sql5";
		logger.info("插入终端贷续贷客户笔数和金额......");
		try {
			List<Object[]> data5 = baseReportService.findObjectsListForService(sql5);
			for (int i = 0; i < data5.size(); i++) {//更新报表每一列的数据
				Object[] b=data5.get(i);
				baseReportService.isnertNewBusiness("update province_business_rank set extend_zd_sum =?,extend_zd_amount=?  where period=? and province =?",b[1],b[2],period,b[0]);
			}
		} catch (Exception e) {
			logger.error("插入终端贷续贷客户笔数和金额出现异常......",e);
		}
		logger.info("插入终端贷续贷客户笔数和金额结束......");
		
		
		
		String sql6="sql.business.sql6";
		logger.info("插入品牌贷续贷客户笔数和金额......");
		try {
			List<Object[]> data6 = baseReportService.findObjectsListForService(sql6);
			for (int i = 0; i < data6.size(); i++) {//更新报表每一列的数据
				Object[] b=data6.get(i);
				baseReportService.isnertNewBusiness("update province_business_rank set extend_bp_sum =?,extend_bp_amount=?  where period=? and province =?",b[1],b[2],period,b[0]);
			}
		} catch (Exception e) {
			logger.error("插入品牌贷续贷客户笔数和金额出现异常......",e);
		}
		logger.info("插入品牌贷续贷客户笔数和金额结束......");
		
		
		
		
		String sql7="sql.business.sql7";
		logger.info("插入续贷客户笔数和金额合计......");
		try {
			List<Object[]> data7 = baseReportService.findObjectsListForService(sql7);
			for (int i = 0; i < data7.size(); i++) {//更新报表每一列的数据
				Object[] b=data7.get(i);
				baseReportService.isnertNewBusiness("update province_business_rank set extend_total_sum =?,extend_total_amount=?  where period=? and province =?",b[1],b[2],period,b[0]);
			}
		} catch (Exception e) {
			logger.error("插入续贷客户笔数和金额合计出现异常......",e);
		}
		logger.info("插入续贷客户笔数和金额合计结束......");
		
		
		
		String sql8="sql.business.sql8";
		logger.info("插入年度笔数和金额合计开始......");
		try {
			List<Object[]> data8 = baseReportService.findObjectsListForService(sql8);
			for (int i = 0; i < data8.size(); i++) {//更新报表每一列的数据
				Object[] b=data8.get(i);
				baseReportService.isnertNewBusiness("update province_business_rank set total_sum =?,total_amount=?  where period=? and province =?",b[1],b[2],period,b[0]);
			}
		} catch (Exception e) {
			logger.error("插入年度笔数和金额合计出现异常......",e);
		}
		logger.info("插入年度笔数和金额合计结束......");
		
		
		logger.info("插入任务完成比例开始......");
			baseReportService.isnertNewBusiness(" update province_business_rank set task_rate = total_amount/year_task,creatdate= now()  where period=? ",period);
		logger.info("插入任务完成比例结束......");
		
		if(period>1){
			String sql9="sql.business.sql9";
			logger.info("插入上期排名开始......");
			try {
				List<Object[]> data9 = baseReportService.findObjectsList(sql9,period-1);
				for (int i = 0; i < data9.size(); i++) {//更新报表每一列的数据
					Object[] b=data9.get(i);
					baseReportService.isnertNewBusiness("update province_business_rank set pre_rank = ?  where period=? and province =?",b[1],period,b[0]);
				}
			} catch (Exception e) {
				logger.error("插入上期排名出现异常......",e);
			}
			logger.info("插入上期排名结束......");
		}else{
			baseReportService.isnertNewBusiness(" update province_business_rank set pre_rank = 0  where period=? ",period);
		}
		
		
		String sql10="sql.business.sql10";
		logger.info("插入本期排名开始......");
		try {
			List<Object[]> data10 = baseReportService.findObjectsList(sql10,period,period);
			for (int i = 0; i < data10.size(); i++) {//更新报表每一列的数据
				Object[] b=data10.get(i);
				baseReportService.isnertNewBusiness("update province_business_rank set this_rank =?  where period=? and province =?",b[1],period,b[0]);
			}
		} catch (Exception e) {
			logger.error("插入本期排名出现异常......",e);
		}
		logger.info("插入本期排名结束......");
		
		String sql11="sql.business.sql11";
		List<Object[]> datas =baseReportService.findObjectsList(sql11,period);
		logger.info("结束同步全国各省区贷款投放排名");
		return datas;
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
}
